Imports System.Data.SqlClient

Friend Class DBScanHistory_Factory
    Inherits DBBase

    Public Function GetOpenHistory( _
        ByVal lServerID As Long, _
        ByVal lDatabaseID As Long, _
        ByVal sObjectType As String, _
        ByVal lObjectID As Long) As ScanHistory

        Dim oHistory As ScanHistory = Nothing

        Dim sSQL As String = "select"
        sSQL = sSQL & "     [ID]"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanHistory"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     ServerID=" & lServerID
        sSQL = sSQL & "     and DatabaseID=" & lDatabaseID
        sSQL = sSQL & "     and ObjectType=" & ValueToDBValue(sObjectType)
        sSQL = sSQL & "     and ObjectID=" & lObjectID
        sSQL = sSQL & "     and ScanComplete is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            oHistory = New ScanHistory(Val(oValue))
            oHistory.Load()
        End If

        Return oHistory
    End Function

    Public Function GetOpenHistory( _
        ByVal lSessionID As Long, _
        ByVal sObjectType As String, _
        ByVal lObjectID As Long) As ScanHistory

        Dim oHistory As ScanHistory = Nothing

        Dim sSQL As String = "select"
        sSQL = sSQL & "     [ID]"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanHistory"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     SessionID=" & lSessionID
        sSQL = sSQL & "     and ObjectType=" & ValueToDBValue(sObjectType)
        sSQL = sSQL & "     and ObjectID=" & lObjectID
        sSQL = sSQL & "     and ScanComplete is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            oHistory = New ScanHistory(Val(oValue))
            oHistory.Load()
        End If

        Return oHistory
    End Function

    Public Sub RemoveCompletedRequests( _
                    ByVal iMinAgeInDays As Integer)

        Dim sSQL As String = "delete from"
        sSQL = sSQL & "     ScanHistory"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     not ScanComplete is null"
        sSQL = sSQL & "     and datediff(day,ScanComplete,current_timestamp)>" & iMinAgeInDays

        MyBase.ExecuteNonQuery(sSQL)
    End Sub

    Public Function GetCountOfIncompleteRequestsForSession(ByVal lSessionID As Long) As Integer
        Dim iCount As Integer = 0

        Dim sSQL As String = "select"
        sSQL = sSQL & "     count(distinct [ID])"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanHistory"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     SessionID=" & lSessionID
        sSQL = sSQL & "     and ScanStart is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            iCount = Val(oValue)
        End If

        Return iCount
    End Function

    Public Function GetCountOfIncompleteRequestsForDatabase(ByVal lDatabaseID As Long) As Integer
        Dim iCount As Integer = 0

        Dim sSQL As String = "select"
        sSQL = sSQL & "     count(distinct [ID])"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanHistory"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     DatabaseID=" & lDatabaseID
        sSQL = sSQL & "     and ScanStart is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            iCount = Val(oValue)
        End If

        Return iCount
    End Function

    Public Function GetCountOfIncompleteRequestsForServer(ByVal lServerID As Long) As Integer
        Dim iCount As Integer = 0

        Dim sSQL As String = "select"
        sSQL = sSQL & "     count(distinct [ID])"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanHistory"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     ServerID=" & lServerID
        sSQL = sSQL & "     and ScanStart is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            iCount = Val(oValue)
        End If

        Return iCount
    End Function

    Public Sub GetTimeOfLastScan( _
                    ByVal lDatabaseID As Long, _
                    ByVal lScanTypeID As ScanSchedule.Type, _
                    ByVal sObjectType As String, _
                    ByVal lObjectID As Long, _
                    ByRef OUTPUT_ScanInProgressStart As String, _
                    ByRef OUTPUT_LastScanComplete As String)

        Dim oData As New DataTable

        Dim sSQL As String = "p_ScanStatus"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@ScanTypeID", SqlDbType.BigInt)
        oParm.Value = lScanTypeID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@ObjectType", SqlDbType.VarChar, 60)
        oParm.Value = sObjectType
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@ObjectID", SqlDbType.BigInt)
        oParm.Value = lObjectID
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        If oData.Rows.Count > 0 Then
            OUTPUT_ScanInProgressStart = oData.Rows(0).Item(0) & ""
            OUTPUT_LastScanComplete = oData.Rows(0).Item(1) & ""
        Else
            OUTPUT_ScanInProgressStart = ""
            OUTPUT_LastScanComplete = ""
        End If
    End Sub

    Public Sub GetTimeOfLastScan( _
                ByVal lDatabaseID As Long, _
                ByVal lScanTypeID As ScanSchedule.Type, _
                ByVal sObjectType As String, _
                ByRef OUTPUT_ScanInProgressStart As String, _
                ByRef OUTPUT_LastScanComplete As String)

        Dim oData As New DataTable

        Dim sSQL As String = "p_ScanStatus"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@ScanTypeID", SqlDbType.BigInt)
        oParm.Value = lScanTypeID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@ObjectType", SqlDbType.VarChar, 60)
        oParm.Value = sObjectType
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        If oData.Rows.Count > 0 Then
            OUTPUT_ScanInProgressStart = oData.Rows(0).Item(0) & ""
            OUTPUT_LastScanComplete = oData.Rows(0).Item(1) & ""
        Else
            OUTPUT_ScanInProgressStart = ""
            OUTPUT_LastScanComplete = ""
        End If
    End Sub

    Public Sub DeleteExpiredScanHistories()
        Dim oData As New DataTable

        Dim sSQL As String = "p_DeleteExpiredScanHistoryData"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        oCMD.ExecuteNonQuery()

        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()
    End Sub
End Class
